package com.jywl.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

import javax.annotation.PostConstruct;
import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import com.jywl.entity.Column;
import com.jywl.entity.Table;

import oracle.jdbc.OracleResultSetMetaData;

@Component
public class JdbcUtil {
	@Autowired
	private  DataSource soaDataSource;
	private static JdbcUtil jdbcUtil;
	

	@PostConstruct
	public void init(){
		jdbcUtil = this;
		jdbcUtil.soaDataSource = this.soaDataSource;
		
	}
    //方法：查询操作
    public static void query(){
        try {
        	Properties p=new Properties();
        	java.util.Properties info = new java.util.Properties();
        	info.put ("user","jywl");
        	info.put ("password","jywl");
        	info.put ("ResultSetMetaDataOptions",1);
        	 Class.forName("oracle.jdbc.driver.OracleDriver");  
   		   //new oracle.jdbc.driver.OracleDriver();  
        	 Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@172.31.11.40:1521:orcl", info);  
           // Connection conn =JdbcUtil.jdbcUtil.soaDataSource.getConnection();
            String sql = "select code,name from JYWL_SUPPLIER where code = 'S051204'";
            Statement state = conn.createStatement();
            //执行查询并返回结果集
            ResultSet rs = state.executeQuery(sql);
            ResultSetMetaData rsmd = rs.getMetaData();
           
            int c = rsmd.getColumnCount();       
            for (int i = 1; i <= c; i++) {
				System.out.println(rsmd.getColumnType(i));
			}                    
            rs.close();
            state.close();
            conn.close();            
            
        } catch (Exception e) {
            e.printStackTrace();
        } 
    }
	public static List<Column> loadSql(String sql) {
		List<Column> tables  = new ArrayList<>();
		 Connection conn = null;  
		 try {
			 	/*Properties p=new Properties();
	        	java.util.Properties info = new java.util.Properties();
	        	info.put ("user","jywl");
	        	info.put ("password","jywl");
	        	info.put ("ResultSetMetaDataOptions",1);
	        	 Class.forName("oracle.jdbc.driver.OracleDriver");  
	   		   //new oracle.jdbc.driver.OracleDriver();  
	   		   conn = DriverManager.getConnection("jdbc:oracle:thin:@172.31.11.40:1521:orcl", info);  */
	          conn =JdbcUtil.jdbcUtil.soaDataSource.getConnection();
	            Statement state = conn.createStatement();
	            //执行查询并返回结果集
	            ResultSet rs = state.executeQuery(sql);
	        /*  rs.next();
	         System.out.println(rs.getString(1)); */
	         
	            ResultSetMetaData rsmd = rs.getMetaData();
	         // System.out.println(rsmd.getTableName(1)+"ssss");  
	            int c = rsmd.getColumnCount();       
	            for (int i = 1; i <= c; i++) {
	            	Column co = new Column();
	            	co.setName(rsmd.getColumnName(i));
	            	co.setComments(rsmd.getColumnLabel(i));
	            	co.setDatatype(rsmd.getColumnTypeName(i));
	            	co.setTablename(rsmd.getTableName(i));
	            	
	            	tables.add(co);
				}
	            
	            rs.close();
	            state.close();
	            conn.close();            
	            
	        } catch (Exception e) {
	            e.printStackTrace();
	        } 
		return tables;
	}
	
	public static void main(String[] args) {  
		  ResultSet rs = null;  
		  Statement stmt = null;  
		  Connection conn = null;  
		  try {  
		   Class.forName("oracle.jdbc.driver.OracleDriver");  
		   //new oracle.jdbc.driver.OracleDriver();  
		   conn = DriverManager.getConnection("jdbc:oracle:thin:@172.31.11.40:1521:orcl", "jywl", "jywl");  
		   stmt = conn.createStatement();  
		   rs = stmt.executeQuery("select * from jywl_order");  
		   while(rs.next()) {  
		    System.out.println(rs.getString("code"));  
		    //System.out.println(rs.getInt("deptno"));  
		   }  
		  } catch (ClassNotFoundException e) {  
		   e.printStackTrace();  
		  } catch (SQLException e) {  
		   e.printStackTrace();  
		  } finally {  
		   try {  
		    if(rs != null) {  
		     rs.close();  
		     rs = null;  
		    }  
		    if(stmt != null) {  
		     stmt.close();  
		     stmt = null;  
		    }  
		    if(conn != null) {  
		     conn.close();  
		     conn = null;  
		    }  
		   } catch (SQLException e) {  
		    e.printStackTrace();  
		   }  
		  }  
		 }  
		  
		
}